package com.ssm.changeunit.persistance;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

/**
 * "Tag" 테이블을 관리하는 DAO의 추상 클래스
 * 
 * @author 시리우스
 */
public class TagDAO extends AbstractTagDAO {

    @Override
    public TagDTO find(String tagName) throws PersistanceException {
        TagDTO dto = null;
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select TAGNAME, CHANGEUNITID from APP.TAG where TAGNAME = '"+tagName+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()){
                dto = new TagDTO();
             
                dto.setTagName(rs.getString("TAGNAME"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing TagDAO.find: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dto; 
    }
    
    @Override
    public List<TagDTO> findByChangeUnitId(String changeUnitId) throws PersistanceException {
        List<TagDTO> dtoArray = new ArrayList<TagDTO>();
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select TAGNAME, CHANGEUNITID from APP.TAG where CHANGEUNITID = '"+changeUnitId+"'";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            if(rs.next()){
                TagDTO dto = new TagDTO();
             
                dto.setTagName(rs.getString("TAGNAME"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));
                
                dtoArray.add(dto);
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing TagDAO.findByChangeUnitId: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dtoArray; 
    }
    
    @Override
    public List<TagDTO> list() throws PersistanceException {
        List<TagDTO> dtoArray = new ArrayList<TagDTO>();
        
        // 검색 루틴 구현
        Connection conn = null; 
        Statement stmt = null; 
        ResultSet rs = null;
        
        //Create SQL Statement 
        String sql="select TAGNAME, CHANGEUNITID from APP.TAG";
        
        try {
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            rs = stmt.executeQuery(sql);
            
            while(rs.next()){
                TagDTO dto = new TagDTO();
                dto.setTagName(rs.getString("TAGNAME"));
                dto.setChangeUnitId(rs.getString("CHANGEUNITID"));

                dtoArray.add(dto);
            }
        }
        catch(SQLException e) { 
            throw new PersistanceException("Exception while executing TagDAO.list: "+ e, e);
        } 
        finally {
            if(rs != null) 
                try {rs.close(); } 
                catch(Exception ignore) { } 
            if(stmt != null) 
                try { stmt.close(); }
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
        // 검색 결과를 리턴 
        return dtoArray; 
    }
 
    @Override
    public void insert(TagDTO tagDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
//        String sql="insert into APP.TAG (TAGNAME, CHANGEUNITID ) " 
//                    + "values ('"+tagDTO.getTagName()+"','"+tagDTO.getChangeUnitId()+"')";
        
        String sql="insert into APP.TAG (TAGNAME, CHANGEUNITID ) " 
                    + "values (?,?)";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, tagDTO.getTagName());
            stmt.setString(2, tagDTO.getChangeUnitId());
            
            stmt.executeUpdate();  
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing TagDAO.insert: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }
    }
 
    @Override
    public void update(TagDTO tagDTO) throws PersistanceException {
        Connection conn = null; 
        PreparedStatement stmt = null;  
        
        //Create SQL Statement 
//        String sql="update APP.TAG set TAGNAME= '"+ tagDTO.getTagName()
//                + "' ,CHANGEUNITID= '"+ tagDTO.getChangeUnitId()
//                + "' where TAGNAME = '"+tagDTO.getTagName()+"'";
        
         String sql="update APP.TAG set TAGNAME=?"
                + " ,CHANGEUNITID=?"
                + " where TAGNAME =?";
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, tagDTO.getTagName());
            stmt.setString(2, tagDTO.getChangeUnitId());
            stmt.setString(3, tagDTO.getTagName());
            
            stmt.executeUpdate();  
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing TagDAO.update: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        }   
    }

    @Override
    public void delete(String tagName) throws PersistanceException {
        Connection conn = null; 
        Statement stmt = null;  
        
        //Create SQL Statement 
        String sql="delete from APP.TAG "  
                   +"WHERE TAGNAME = '"+tagName+"'";  
        
        try { 
            conn = dm.getConnection(); 
            conn.setAutoCommit(true);   
            stmt = conn.createStatement(); 
            stmt.executeUpdate(sql); 
        }
        catch(SQLException e) {
            throw new PersistanceException("Exception while executing TagDAO.delete: "+ e, e); 
        } 
        finally {
            if(stmt != null) 
                try { stmt.close(); } 
                catch(Exception ignore) { }  
            if(conn != null) 
                try { conn.close(); } 
                catch(Exception ignore) { } 
        } 
    }
}
